/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package DA;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Guigo
 */
public class DAUnidadesDT {

    String conexionString = "jdbc:sqlserver://localhost:1433;"
            + "databaseName=Viaticos;user=sa; password=admin;";

    Connection con = null;

    public ResultSet getAllUDT() throws SQLException {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(conexionString);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DAFuncionarios.class.getName()).log(Level.SEVERE, null, ex);
        }
        String query = "SELECT * FROM Unidad_De_Trabajo";
        Statement stmnt = con.createStatement();
        return stmnt.executeQuery(query);
    }

    public ResultSet getUDT(String gerencia) throws SQLException {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(conexionString);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DAFuncionarios.class.getName()).log(Level.SEVERE, null, ex);
        }
        String query = "SELECT Nombre FROM Unidad_De_Trabajo WHERE Gererencia LIKE '%"
                + gerencia + "%'";
        Statement stmnt = con.createStatement();
        return stmnt.executeQuery(query);
    }

    public ResultSet getAllGerencias() throws SQLException {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(conexionString);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DAFuncionarios.class.getName()).log(Level.SEVERE, null, ex);
        }
        String query = "SELECT Gererencia FROM Unidad_De_Trabajo GROUP BY Gererencia";
        Statement stmnt = con.createStatement();
        return stmnt.executeQuery(query);
    }

    public int getUDT(String gerencia, String uProg) throws SQLException {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(conexionString);
            String query = "SELECT UnidadProgramatica FROM Unidad_De_Trabajo WHERE "
                    + "Gererencia LIKE '%" + gerencia + "%' AND "
                    + "Nombre LIKE '%" + uProg + "%'";
            Statement stmnt = con.createStatement();
            ResultSet rs = stmnt.executeQuery(query);

            if (rs.next()) {
                return rs.getInt("UnidadProgramatica");
            } else {
                return -1;
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DAFuncionarios.class.getName()).log(Level.SEVERE, null, ex);
        }
        return -1;
    }
}
